package com.hnkywl.sts.dao.tjbb;

import com.hnkywl.sts.entity.sfgl.Sfk;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 流程实际利率
 * Created with IntelliJ IDEA.
 * User: xx
 * Date: 13-6-17
 * Time: 下午4:32
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class LjsjllDao extends SimpleHibernateTemplate<Sfk, Long> {
    /**
     * 流程实际利率数据列表
     *
     * @param ljid
     * @param timeFrom
     * @param timeTo
     * @return
     */
    public List<Map> getLjll(Long ljid, String timeFrom, String timeTo) {
        String sql = getSq1(timeFrom, timeTo);
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setLong("ljid", ljid);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    /**
     * @param timeFrom
     * @param timeTo
     * @return
     */
    public String getSq1(String timeFrom, String timeTo) {
        String strSql = "select c.id cgd_id," +
                "               c.cgdh," +
                "               (select j.je from t_biz_jsd j where j.id = c.cgjs_id) as cgdje," +
                "               x.id xsd_id," +
                "               x.xsdh," +
                "               (select j.je from t_biz_jsd j where j.id = x.xsjsd_id) as xsdje," +
                "               c.date_created" +
                "          from t_biz_cgd c, t_biz_xsd x" +
                "         where c.xsd_id = x.id" +
                "           and c.yjs = 1" +
                "           and c.lj_id = :ljid";
        if (null != timeFrom && !"".equals(timeFrom)) {    //时间从
            strSql += " and c.last_updated > to_date('" + timeFrom + " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
        }
        if (null != timeTo && !"".equals(timeTo)) {      //时间到
            strSql += " and c.last_updated <= to_date('" + timeTo + " 24:59:59','yyyy-mm-dd  hh24:mi:ss')";
        }

        return strSql;
    }
}
